
<html><HEAD>
<LINK REL=STYLESHEET HREF="default.css" TYPE="text/css">
<TITLE>
Using Oracle user-defined types</TITLE>
</HEAD>
<BODY>

<!-- Header -->
<p class="ancestor" align="right"><A HREF="connpbp69.htm">Previous</A>&nbsp;&nbsp;<A HREF="connpbp71.htm" >Next</A>
<!-- End Header -->
<A NAME="CCJBEBBI"></A><h1>Using Oracle user-defined types</h1>
<A NAME="TI1487"></A><p>PowerBuilder supports <ACRONYM title = "sequel" >SQL</ACRONYM> <b>CREATE
TYPE</b> and <b>CREATE TABLE</b> statements for
Oracle user-defined types (objects) in the <ACRONYM title = "I sequel" >ISQL</ACRONYM> view of the Database painter.
It correctly handles <ACRONYM title = "sequel" >SQL</ACRONYM> <b>SELECT</b>, <b>INSERT</b>, <b>UPDATE</b>,
and <b>DELETE</b> statements for user-defined types
in the Database and DataWindow painters.</p>
<A NAME="TI1488"></A><p>This means that using the Oracle native database interfaces
in PowerBuilder, you can:</p>
<A NAME="TI1489"></A><table cellspacing=0 cellpadding=6 border=1 frame="void" rules="all"><tr><th  rowspan="1"  ><A NAME="TI1490"></A>Do this</th>
<th  rowspan="1"  ><A NAME="TI1491"></A>In</th>
</tr>
<tr><td  rowspan="1"  ><A NAME="TI1492"></A>Use Oracle syntax to create user-defined
types</td>
<td  rowspan="1"  ><A NAME="TI1493"></A>Database painter</td>
</tr>
<tr><td  rowspan="1"  ><A NAME="TI1494"></A>Use Oracle syntax to create tables with
columns that reference user-defined types</td>
<td  rowspan="1"  ><A NAME="TI1495"></A>Database painter</td>
</tr>
<tr><td  rowspan="1"  ><A NAME="TI1496"></A>View columns in Oracle tables that reference user-defined
types</td>
<td  rowspan="1"  ><A NAME="TI1497"></A>Database painter</td>
</tr>
<tr><td  rowspan="1"  ><A NAME="TI1498"></A>Manipulate data in Oracle tables that
have user-defined types</td>
<td  rowspan="1"  ><A NAME="TI1499"></A>Database painter<A NAME="TI1500"></A><p>DataWindow painter</p><A NAME="TI1501"></A><p>DataWindow objects</p></td>
</tr>
<tr><td  rowspan="1"  ><A NAME="TI1502"></A>Export Oracle table syntax containing
user-defined types to a log file</td>
<td  rowspan="1"  ><A NAME="TI1503"></A>Database painter</td>
</tr>
<tr><td  rowspan="1"  ><A NAME="TI1504"></A>Invoke methods </td>
<td  rowspan="1"  ><A NAME="TI1505"></A>DataWindow painter (Compute tab in <ACRONYM title = "sequel" >SQL</ACRONYM> Toolbox)</td>
</tr>
</table>
<A NAME="TI1506"></A><h4>Example</h4>
<A NAME="TI1507"></A><p>Here is a simple example that shows how you might create and
use Oracle user-defined types in PowerBuilder.</p>
<A NAME="TI1508"></A><p>For more information about Oracle user-defined
types, see your Oracle documentation.</p>
<A NAME="TI1509"></A><p><img src="images/proc.gif" width=17 height=17 border=0 align="bottom" alt="Steps"> To create and use Oracle user-defined types:</p>
<ol><li class=fi><p>In the <ACRONYM title = "I sequel" >ISQL</ACRONYM> view
of the Database painter, create two Oracle user-defined types: ball_stats_type
and player_type.</p><p>Here is the Oracle syntax to create <b>ball_stats_type</b>.
Notice that the <b>ball_stats</b> object
of type <b>ball_stats_type</b> has
a method associated with it called <b>get_avg</b>.<p><PRE> CREATE OR REPLACE TYPE ball_stats_type AS OBJECT (bat_avg NUMBER(4,3),rbi NUMBER(3),MEMBER FUNCTION get_avg RETURN NUMBER,PRAGMA RESTRICT_REFERENCES    (get_avg,WNDS,RNPS,WNPS));<br>CREATE OR REPLACE TYPE BODY ball_stats_type ASMEMBER FUNCTION get_avg RETURN NUMBER ISBEGINRETURN SELF.bat_avg;<br>END;<br>END;</PRE></p><p>Here is the Oracle <ACRONYM title = "sequel" >SQL</ACRONYM> syntax
to create <b>player_type</b>. <b>Player_type</b> references
the user-defined type <b>ball_stats_type</b>. PowerBuilder supports such
nesting graphically in the Database, DataWindow, and Table painters (see
step 3).<p><PRE> CREATE TYPE player_type AS OBJECT (player_no NUMBER(2),player_name VARCHAR2(30),ball_stats ball_stats_type);</PRE></p></li>
<li class=ds><p>In the Database painter, create a table named
lineup that references these user-defined types.</p><p>Here is the Oracle <ACRONYM title = "sequel" >SQL</ACRONYM> syntax
to create the lineup table and insert a row. Lineup references the <b>player_type</b> user-defined
type.<p><PRE> CREATE TABLE lineup (position NUMBER(2) NOT NULL, player player_type);<br>INSERT INTO lineup VALUES (1,player_type (15, 'Dustin Pedroia', ball_stats_type (0.317, 50)));</PRE></p></li>
<li class=ds><p>Display the lineup table in the Database or DataWindow painter.</p><p>PowerBuilder uses the following structure-&gt;member notation
to display the table:<p><PRE> lineup</PRE><PRE> ======</PRE><PRE> position</PRE><PRE> player-&gt;player_no</PRE><PRE> player-&gt;player_name</PRE><PRE> player-&gt;ball_stats-&gt;bat_avg</PRE><PRE> player-&gt;ball_stats-&gt;rbi</PRE></p></li>
<li class=ds><p>To access the <b>get_avg</b> method
of the object ball_stats contained in the object column
player, use the following <b>structure-&gt;member</b> notation
when defining a computed column for the DataWindow object. For example, when
working in the DataWindow painter, you could use this notation on the Compute
tab in the <ACRONYM title = "sequel" >SQL</ACRONYM> Toolbox:</p><p><p><PRE> player-&gt;ball_stats-&gt;get_avg()</PRE></p></li></ol>
<br>
